﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Runtime.InteropServices;
namespace Common
{
    public class ExcelHelper
    {
        public static DataTable GetData(string strPath, string sheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;";
                using (OleDbConnection con = new OleDbConnection(strCon))
                {
                    con.Open();
                    System.Data.DataTable dtTemp = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
                String strCmd = "select * from [" + sheetName + "]";
                OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon);
                cmd.Fill(dt);
            }
            catch (Exception ex) { throw ex; }
            return dt;
        }

        public static string[] GetExcelSheetNames(string filePath)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            int count = wb.Worksheets.Count;
            string[] names = new string[count];
            for (int i = 1; i <= count; i++)
            {
                names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
            }
            KillSpecialExcel(excelApp);
            return names;
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        private static void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Delete Excel Process Error:" + ex.Message);
            }
        }
    }
}
